
USE [caogiaphat2]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Activity_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Activity_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Activity_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Activity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Activity_Get_List

AS


				
				SELECT
					[ID],
					[SessionId],
					[UserId],
					[Ip],
					[DateLogin],
					[DateLogout],
					[Description]
				FROM
					[dbo].[Activity]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Activity_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Activity_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Activity_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Activity table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Activity_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Activity]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[SessionId], O.[UserId], O.[Ip], O.[DateLogin], O.[DateLogout], O.[Description]
				FROM
				    [dbo].[Activity] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Activity]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Activity_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Activity_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Activity_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Activity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Activity_Insert
(

	@Id int    OUTPUT,

	@SessionId nvarchar (50)  ,

	@UserId int   ,

	@Ip nvarchar (50)  ,

	@DateLogin datetime   ,

	@DateLogout datetime   ,

	@Description nvarchar (50)  
)
AS


				
				INSERT INTO [dbo].[Activity]
					(
					[SessionId]
					,[UserId]
					,[Ip]
					,[DateLogin]
					,[DateLogout]
					,[Description]
					)
				VALUES
					(
					@SessionId
					,@UserId
					,@Ip
					,@DateLogin
					,@DateLogout
					,@Description
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Activity_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Activity_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Activity_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Activity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Activity_Update
(

	@Id int   ,

	@SessionId nvarchar (50)  ,

	@UserId int   ,

	@Ip nvarchar (50)  ,

	@DateLogin datetime   ,

	@DateLogout datetime   ,

	@Description nvarchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Activity]
				SET
					[SessionId] = @SessionId
					,[UserId] = @UserId
					,[Ip] = @Ip
					,[DateLogin] = @DateLogin
					,[DateLogout] = @DateLogout
					,[Description] = @Description
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Activity_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Activity_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Activity_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Activity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Activity_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Activity] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Activity_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Activity_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Activity_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Activity table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Activity_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[SessionId],
					[UserId],
					[Ip],
					[DateLogin],
					[DateLogout],
					[Description]
				FROM
					[dbo].[Activity]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Activity_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Activity_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Activity_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Activity table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Activity_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@SessionId nvarchar (50)  = null ,

	@UserId int   = null ,

	@Ip nvarchar (50)  = null ,

	@DateLogin datetime   = null ,

	@DateLogout datetime   = null ,

	@Description nvarchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [SessionId]
	, [UserId]
	, [Ip]
	, [DateLogin]
	, [DateLogout]
	, [Description]
    FROM
	[dbo].[Activity]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([SessionId] = @SessionId OR @SessionId IS NULL)
	AND ([UserId] = @UserId OR @UserId IS NULL)
	AND ([Ip] = @Ip OR @Ip IS NULL)
	AND ([DateLogin] = @DateLogin OR @DateLogin IS NULL)
	AND ([DateLogout] = @DateLogout OR @DateLogout IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [SessionId]
	, [UserId]
	, [Ip]
	, [DateLogin]
	, [DateLogout]
	, [Description]
    FROM
	[dbo].[Activity]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([SessionId] = @SessionId AND @SessionId is not null)
	OR ([UserId] = @UserId AND @UserId is not null)
	OR ([Ip] = @Ip AND @Ip is not null)
	OR ([DateLogin] = @DateLogin AND @DateLogin is not null)
	OR ([DateLogout] = @DateLogout AND @DateLogout is not null)
	OR ([Description] = @Description AND @Description is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Slide_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Slide_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Slide_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Slide table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Slide_Get_List

AS


				
				SELECT
					[ID],
					[ImagePath],
					[Title],
					[ImageType],
					[Category],
					[IsDisplay]
				FROM
					[dbo].[Slide]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Slide_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Slide_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Slide_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Slide table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Slide_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Slide]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[ImagePath], O.[Title], O.[ImageType], O.[Category], O.[IsDisplay]
				FROM
				    [dbo].[Slide] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Slide]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Slide_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Slide_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Slide_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Slide table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Slide_Insert
(

	@Id int    OUTPUT,

	@ImagePath nvarchar (50)  ,

	@Title nvarchar (50)  ,

	@ImageType nvarchar (50)  ,

	@Category nvarchar (200)  ,

	@IsDisplay bit   
)
AS


				
				INSERT INTO [dbo].[Slide]
					(
					[ImagePath]
					,[Title]
					,[ImageType]
					,[Category]
					,[IsDisplay]
					)
				VALUES
					(
					@ImagePath
					,@Title
					,@ImageType
					,@Category
					,@IsDisplay
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Slide_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Slide_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Slide_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Slide table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Slide_Update
(

	@Id int   ,

	@ImagePath nvarchar (50)  ,

	@Title nvarchar (50)  ,

	@ImageType nvarchar (50)  ,

	@Category nvarchar (200)  ,

	@IsDisplay bit   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Slide]
				SET
					[ImagePath] = @ImagePath
					,[Title] = @Title
					,[ImageType] = @ImageType
					,[Category] = @Category
					,[IsDisplay] = @IsDisplay
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Slide_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Slide_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Slide_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Slide table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Slide_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Slide] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Slide_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Slide_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Slide_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Slide table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Slide_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[ImagePath],
					[Title],
					[ImageType],
					[Category],
					[IsDisplay]
				FROM
					[dbo].[Slide]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Slide_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Slide_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Slide_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Slide table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Slide_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@ImagePath nvarchar (50)  = null ,

	@Title nvarchar (50)  = null ,

	@ImageType nvarchar (50)  = null ,

	@Category nvarchar (200)  = null ,

	@IsDisplay bit   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [ImagePath]
	, [Title]
	, [ImageType]
	, [Category]
	, [IsDisplay]
    FROM
	[dbo].[Slide]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([ImagePath] = @ImagePath OR @ImagePath IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
	AND ([ImageType] = @ImageType OR @ImageType IS NULL)
	AND ([Category] = @Category OR @Category IS NULL)
	AND ([IsDisplay] = @IsDisplay OR @IsDisplay IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [ImagePath]
	, [Title]
	, [ImageType]
	, [Category]
	, [IsDisplay]
    FROM
	[dbo].[Slide]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([ImagePath] = @ImagePath AND @ImagePath is not null)
	OR ([Title] = @Title AND @Title is not null)
	OR ([ImageType] = @ImageType AND @ImageType is not null)
	OR ([Category] = @Category AND @Category is not null)
	OR ([IsDisplay] = @IsDisplay AND @IsDisplay is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the UserRoles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_Get_List

AS


				
				SELECT
					[ID],
					[UserId],
					[RoleId]
				FROM
					[dbo].[UserRoles]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the UserRoles table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[UserRoles]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[UserId], O.[RoleId]
				FROM
				    [dbo].[UserRoles] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[UserRoles]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the UserRoles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_Insert
(

	@Id int    OUTPUT,

	@UserId int   ,

	@RoleId int   
)
AS


				
				INSERT INTO [dbo].[UserRoles]
					(
					[UserId]
					,[RoleId]
					)
				VALUES
					(
					@UserId
					,@RoleId
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the UserRoles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_Update
(

	@Id int   ,

	@UserId int   ,

	@RoleId int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[UserRoles]
				SET
					[UserId] = @UserId
					,[RoleId] = @RoleId
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the UserRoles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[UserRoles] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_GetByRoleId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_GetByRoleId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_GetByRoleId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the UserRoles table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_GetByRoleId
(

	@RoleId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[ID],
					[UserId],
					[RoleId]
				FROM
					[dbo].[UserRoles]
				WHERE
					[RoleId] = @RoleId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_GetByUserId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_GetByUserId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_GetByUserId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the UserRoles table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_GetByUserId
(

	@UserId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[ID],
					[UserId],
					[RoleId]
				FROM
					[dbo].[UserRoles]
				WHERE
					[UserId] = @UserId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the UserRoles table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[UserId],
					[RoleId]
				FROM
					[dbo].[UserRoles]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserRoles_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserRoles_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserRoles_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the UserRoles table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserRoles_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@UserId int   = null ,

	@RoleId int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [UserId]
	, [RoleId]
    FROM
	[dbo].[UserRoles]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([UserId] = @UserId OR @UserId IS NULL)
	AND ([RoleId] = @RoleId OR @RoleId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [UserId]
	, [RoleId]
    FROM
	[dbo].[UserRoles]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([UserId] = @UserId AND @UserId is not null)
	OR ([RoleId] = @RoleId AND @RoleId is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Get_List

AS


				
				SELECT
					[ID],
					[Title],
					[Alias],
					[Link],
					[ParentId],
					[OrderIndex],
					[IsVisible],
					[Language],
					[MenuPosition],
					[CreatedDate],
					[UpdatedDate],
					[UpdatedUser],
					[CreatedUser]
				FROM
					[dbo].[Category]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Category table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[Title], O.[Alias], O.[Link], O.[ParentId], O.[OrderIndex], O.[IsVisible], O.[Language], O.[MenuPosition], O.[CreatedDate], O.[UpdatedDate], O.[UpdatedUser], O.[CreatedUser]
				FROM
				    [dbo].[Category] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Insert
(

	@Id int    OUTPUT,

	@Title nvarchar (200)  ,

	@Alias nvarchar (200)  ,

	@Link nvarchar (200)  ,

	@ParentId int   ,

	@OrderIndex int   ,

	@IsVisible bit   ,

	@Language varchar (10)  ,

	@MenuPosition varchar (10)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@UpdatedUser varchar (50)  ,

	@CreatedUser varchar (50)  
)
AS


				
				INSERT INTO [dbo].[Category]
					(
					[Title]
					,[Alias]
					,[Link]
					,[ParentId]
					,[OrderIndex]
					,[IsVisible]
					,[Language]
					,[MenuPosition]
					,[CreatedDate]
					,[UpdatedDate]
					,[UpdatedUser]
					,[CreatedUser]
					)
				VALUES
					(
					@Title
					,@Alias
					,@Link
					,@ParentId
					,@OrderIndex
					,@IsVisible
					,@Language
					,@MenuPosition
					,@CreatedDate
					,@UpdatedDate
					,@UpdatedUser
					,@CreatedUser
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Update
(

	@Id int   ,

	@Title nvarchar (200)  ,

	@Alias nvarchar (200)  ,

	@Link nvarchar (200)  ,

	@ParentId int   ,

	@OrderIndex int   ,

	@IsVisible bit   ,

	@Language varchar (10)  ,

	@MenuPosition varchar (10)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@UpdatedUser varchar (50)  ,

	@CreatedUser varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Category]
				SET
					[Title] = @Title
					,[Alias] = @Alias
					,[Link] = @Link
					,[ParentId] = @ParentId
					,[OrderIndex] = @OrderIndex
					,[IsVisible] = @IsVisible
					,[Language] = @Language
					,[MenuPosition] = @MenuPosition
					,[CreatedDate] = @CreatedDate
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedUser] = @UpdatedUser
					,[CreatedUser] = @CreatedUser
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Category] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Category table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[Title],
					[Alias],
					[Link],
					[ParentId],
					[OrderIndex],
					[IsVisible],
					[Language],
					[MenuPosition],
					[CreatedDate],
					[UpdatedDate],
					[UpdatedUser],
					[CreatedUser]
				FROM
					[dbo].[Category]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Category table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Title nvarchar (200)  = null ,

	@Alias nvarchar (200)  = null ,

	@Link nvarchar (200)  = null ,

	@ParentId int   = null ,

	@OrderIndex int   = null ,

	@IsVisible bit   = null ,

	@Language varchar (10)  = null ,

	@MenuPosition varchar (10)  = null ,

	@CreatedDate datetime   = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedUser varchar (50)  = null ,

	@CreatedUser varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Alias]
	, [Link]
	, [ParentId]
	, [OrderIndex]
	, [IsVisible]
	, [Language]
	, [MenuPosition]
	, [CreatedDate]
	, [UpdatedDate]
	, [UpdatedUser]
	, [CreatedUser]
    FROM
	[dbo].[Category]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
	AND ([Alias] = @Alias OR @Alias IS NULL)
	AND ([Link] = @Link OR @Link IS NULL)
	AND ([ParentId] = @ParentId OR @ParentId IS NULL)
	AND ([OrderIndex] = @OrderIndex OR @OrderIndex IS NULL)
	AND ([IsVisible] = @IsVisible OR @IsVisible IS NULL)
	AND ([Language] = @Language OR @Language IS NULL)
	AND ([MenuPosition] = @MenuPosition OR @MenuPosition IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedUser] = @UpdatedUser OR @UpdatedUser IS NULL)
	AND ([CreatedUser] = @CreatedUser OR @CreatedUser IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Alias]
	, [Link]
	, [ParentId]
	, [OrderIndex]
	, [IsVisible]
	, [Language]
	, [MenuPosition]
	, [CreatedDate]
	, [UpdatedDate]
	, [UpdatedUser]
	, [CreatedUser]
    FROM
	[dbo].[Category]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([Title] = @Title AND @Title is not null)
	OR ([Alias] = @Alias AND @Alias is not null)
	OR ([Link] = @Link AND @Link is not null)
	OR ([ParentId] = @ParentId AND @ParentId is not null)
	OR ([OrderIndex] = @OrderIndex AND @OrderIndex is not null)
	OR ([IsVisible] = @IsVisible AND @IsVisible is not null)
	OR ([Language] = @Language AND @Language is not null)
	OR ([MenuPosition] = @MenuPosition AND @MenuPosition is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedUser] = @UpdatedUser AND @UpdatedUser is not null)
	OR ([CreatedUser] = @CreatedUser AND @CreatedUser is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Get_List

AS


				
				SELECT
					[ID],
					[UserName],
					[Title],
					[FirstName],
					[LastName],
					[Email],
					[Address],
					[Phone],
					[Password],
					[PasswordSalt],
					[LastLogin],
					[UserType],
					[IsActivity],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Users]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Users table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Users]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[UserName], O.[Title], O.[FirstName], O.[LastName], O.[Email], O.[Address], O.[Phone], O.[Password], O.[PasswordSalt], O.[LastLogin], O.[UserType], O.[IsActivity], O.[CreatedDate], O.[UpdatedDate], O.[CreatedUser], O.[UpdatedUser]
				FROM
				    [dbo].[Users] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Users]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Insert
(

	@Id int    OUTPUT,

	@UserName varchar (50)  ,

	@Title nvarchar (20)  ,

	@FirstName nvarchar (50)  ,

	@LastName nvarchar (50)  ,

	@Email nvarchar (50)  ,

	@Address nvarchar (100)  ,

	@Phone nchar (12)  ,

	@Password nvarchar (50)  ,

	@PasswordSalt nvarchar (50)  ,

	@LastLogin datetime   ,

	@UserType int   ,

	@IsActivity bit   ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (50)  ,

	@UpdatedUser varchar (50)  
)
AS


				
				INSERT INTO [dbo].[Users]
					(
					[UserName]
					,[Title]
					,[FirstName]
					,[LastName]
					,[Email]
					,[Address]
					,[Phone]
					,[Password]
					,[PasswordSalt]
					,[LastLogin]
					,[UserType]
					,[IsActivity]
					,[CreatedDate]
					,[UpdatedDate]
					,[CreatedUser]
					,[UpdatedUser]
					)
				VALUES
					(
					@UserName
					,@Title
					,@FirstName
					,@LastName
					,@Email
					,@Address
					,@Phone
					,@Password
					,@PasswordSalt
					,@LastLogin
					,@UserType
					,@IsActivity
					,@CreatedDate
					,@UpdatedDate
					,@CreatedUser
					,@UpdatedUser
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Update
(

	@Id int   ,

	@UserName varchar (50)  ,

	@Title nvarchar (20)  ,

	@FirstName nvarchar (50)  ,

	@LastName nvarchar (50)  ,

	@Email nvarchar (50)  ,

	@Address nvarchar (100)  ,

	@Phone nchar (12)  ,

	@Password nvarchar (50)  ,

	@PasswordSalt nvarchar (50)  ,

	@LastLogin datetime   ,

	@UserType int   ,

	@IsActivity bit   ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (50)  ,

	@UpdatedUser varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Users]
				SET
					[UserName] = @UserName
					,[Title] = @Title
					,[FirstName] = @FirstName
					,[LastName] = @LastName
					,[Email] = @Email
					,[Address] = @Address
					,[Phone] = @Phone
					,[Password] = @Password
					,[PasswordSalt] = @PasswordSalt
					,[LastLogin] = @LastLogin
					,[UserType] = @UserType
					,[IsActivity] = @IsActivity
					,[CreatedDate] = @CreatedDate
					,[UpdatedDate] = @UpdatedDate
					,[CreatedUser] = @CreatedUser
					,[UpdatedUser] = @UpdatedUser
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Users] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_GetByUserType procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_GetByUserType') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_GetByUserType
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Users table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_GetByUserType
(

	@UserType int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[ID],
					[UserName],
					[Title],
					[FirstName],
					[LastName],
					[Email],
					[Address],
					[Phone],
					[Password],
					[PasswordSalt],
					[LastLogin],
					[UserType],
					[IsActivity],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Users]
				WHERE
					[UserType] = @UserType
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Users table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[UserName],
					[Title],
					[FirstName],
					[LastName],
					[Email],
					[Address],
					[Phone],
					[Password],
					[PasswordSalt],
					[LastLogin],
					[UserType],
					[IsActivity],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Users]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Users table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@UserName varchar (50)  = null ,

	@Title nvarchar (20)  = null ,

	@FirstName nvarchar (50)  = null ,

	@LastName nvarchar (50)  = null ,

	@Email nvarchar (50)  = null ,

	@Address nvarchar (100)  = null ,

	@Phone nchar (12)  = null ,

	@Password nvarchar (50)  = null ,

	@PasswordSalt nvarchar (50)  = null ,

	@LastLogin datetime   = null ,

	@UserType int   = null ,

	@IsActivity bit   = null ,

	@CreatedDate datetime   = null ,

	@UpdatedDate datetime   = null ,

	@CreatedUser varchar (50)  = null ,

	@UpdatedUser varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [UserName]
	, [Title]
	, [FirstName]
	, [LastName]
	, [Email]
	, [Address]
	, [Phone]
	, [Password]
	, [PasswordSalt]
	, [LastLogin]
	, [UserType]
	, [IsActivity]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
    FROM
	[dbo].[Users]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([UserName] = @UserName OR @UserName IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
	AND ([FirstName] = @FirstName OR @FirstName IS NULL)
	AND ([LastName] = @LastName OR @LastName IS NULL)
	AND ([Email] = @Email OR @Email IS NULL)
	AND ([Address] = @Address OR @Address IS NULL)
	AND ([Phone] = @Phone OR @Phone IS NULL)
	AND ([Password] = @Password OR @Password IS NULL)
	AND ([PasswordSalt] = @PasswordSalt OR @PasswordSalt IS NULL)
	AND ([LastLogin] = @LastLogin OR @LastLogin IS NULL)
	AND ([UserType] = @UserType OR @UserType IS NULL)
	AND ([IsActivity] = @IsActivity OR @IsActivity IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([CreatedUser] = @CreatedUser OR @CreatedUser IS NULL)
	AND ([UpdatedUser] = @UpdatedUser OR @UpdatedUser IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [UserName]
	, [Title]
	, [FirstName]
	, [LastName]
	, [Email]
	, [Address]
	, [Phone]
	, [Password]
	, [PasswordSalt]
	, [LastLogin]
	, [UserType]
	, [IsActivity]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
    FROM
	[dbo].[Users]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([UserName] = @UserName AND @UserName is not null)
	OR ([Title] = @Title AND @Title is not null)
	OR ([FirstName] = @FirstName AND @FirstName is not null)
	OR ([LastName] = @LastName AND @LastName is not null)
	OR ([Email] = @Email AND @Email is not null)
	OR ([Address] = @Address AND @Address is not null)
	OR ([Phone] = @Phone AND @Phone is not null)
	OR ([Password] = @Password AND @Password is not null)
	OR ([PasswordSalt] = @PasswordSalt AND @PasswordSalt is not null)
	OR ([LastLogin] = @LastLogin AND @LastLogin is not null)
	OR ([UserType] = @UserType AND @UserType is not null)
	OR ([IsActivity] = @IsActivity AND @IsActivity is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([CreatedUser] = @CreatedUser AND @CreatedUser is not null)
	OR ([UpdatedUser] = @UpdatedUser AND @UpdatedUser is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Roles_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Roles_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Roles_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Roles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Roles_Get_List

AS


				
				SELECT
					[ID],
					[Role],
					[Description]
				FROM
					[dbo].[Roles]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Roles_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Roles_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Roles_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Roles table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Roles_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Roles]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[Role], O.[Description]
				FROM
				    [dbo].[Roles] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Roles]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Roles_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Roles_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Roles_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Roles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Roles_Insert
(

	@Id int    OUTPUT,

	@Role nvarchar (50)  ,

	@Description nvarchar (150)  
)
AS


				
				INSERT INTO [dbo].[Roles]
					(
					[Role]
					,[Description]
					)
				VALUES
					(
					@Role
					,@Description
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Roles_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Roles_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Roles_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Roles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Roles_Update
(

	@Id int   ,

	@Role nvarchar (50)  ,

	@Description nvarchar (150)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Roles]
				SET
					[Role] = @Role
					,[Description] = @Description
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Roles_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Roles_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Roles_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Roles table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Roles_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Roles] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Roles_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Roles_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Roles_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Roles table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Roles_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[Role],
					[Description]
				FROM
					[dbo].[Roles]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Roles_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Roles_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Roles_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Roles table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Roles_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Role nvarchar (50)  = null ,

	@Description nvarchar (150)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [Role]
	, [Description]
    FROM
	[dbo].[Roles]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([Role] = @Role OR @Role IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [Role]
	, [Description]
    FROM
	[dbo].[Roles]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([Role] = @Role AND @Role is not null)
	OR ([Description] = @Description AND @Description is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Menu table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_Get_List

AS


				
				SELECT
					[ID],
					[Title],
					[Alias],
					[Link],
					[ParentId],
					[OrderIndex],
					[IsVisible],
					[MenuType],
					[MenuPosition],
					[Summary],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser],
					[IsPublished],
					[Language]
				FROM
					[dbo].[Menu]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Menu table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Menu]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[Title], O.[Alias], O.[Link], O.[ParentId], O.[OrderIndex], O.[IsVisible], O.[MenuType], O.[MenuPosition], O.[Summary], O.[CreatedDate], O.[UpdatedDate], O.[CreatedUser], O.[UpdatedUser], O.[IsPublished], O.[Language]
				FROM
				    [dbo].[Menu] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Menu]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Menu table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_Insert
(

	@Id int    OUTPUT,

	@Title nvarchar (200)  ,

	@Alias varchar (200)  ,

	@Link nvarchar (200)  ,

	@ParentId int   ,

	@OrderIndex int   ,

	@IsVisible bit   ,

	@MenuType int   ,

	@MenuPosition varchar (10)  ,

	@Summary nvarchar (MAX)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (10)  ,

	@UpdatedUser varchar (10)  ,

	@IsPublished bit   ,

	@Language varchar (10)  
)
AS


				
				INSERT INTO [dbo].[Menu]
					(
					[Title]
					,[Alias]
					,[Link]
					,[ParentId]
					,[OrderIndex]
					,[IsVisible]
					,[MenuType]
					,[MenuPosition]
					,[Summary]
					,[CreatedDate]
					,[UpdatedDate]
					,[CreatedUser]
					,[UpdatedUser]
					,[IsPublished]
					,[Language]
					)
				VALUES
					(
					@Title
					,@Alias
					,@Link
					,@ParentId
					,@OrderIndex
					,@IsVisible
					,@MenuType
					,@MenuPosition
					,@Summary
					,@CreatedDate
					,@UpdatedDate
					,@CreatedUser
					,@UpdatedUser
					,@IsPublished
					,@Language
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Menu table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_Update
(

	@Id int   ,

	@Title nvarchar (200)  ,

	@Alias varchar (200)  ,

	@Link nvarchar (200)  ,

	@ParentId int   ,

	@OrderIndex int   ,

	@IsVisible bit   ,

	@MenuType int   ,

	@MenuPosition varchar (10)  ,

	@Summary nvarchar (MAX)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (10)  ,

	@UpdatedUser varchar (10)  ,

	@IsPublished bit   ,

	@Language varchar (10)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Menu]
				SET
					[Title] = @Title
					,[Alias] = @Alias
					,[Link] = @Link
					,[ParentId] = @ParentId
					,[OrderIndex] = @OrderIndex
					,[IsVisible] = @IsVisible
					,[MenuType] = @MenuType
					,[MenuPosition] = @MenuPosition
					,[Summary] = @Summary
					,[CreatedDate] = @CreatedDate
					,[UpdatedDate] = @UpdatedDate
					,[CreatedUser] = @CreatedUser
					,[UpdatedUser] = @UpdatedUser
					,[IsPublished] = @IsPublished
					,[Language] = @Language
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Menu table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Menu] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_GetByMenuType procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_GetByMenuType') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_GetByMenuType
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Menu table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_GetByMenuType
(

	@MenuType int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[ID],
					[Title],
					[Alias],
					[Link],
					[ParentId],
					[OrderIndex],
					[IsVisible],
					[MenuType],
					[MenuPosition],
					[Summary],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser],
					[IsPublished],
					[Language]
				FROM
					[dbo].[Menu]
				WHERE
					[MenuType] = @MenuType
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Menu table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[Title],
					[Alias],
					[Link],
					[ParentId],
					[OrderIndex],
					[IsVisible],
					[MenuType],
					[MenuPosition],
					[Summary],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser],
					[IsPublished],
					[Language]
				FROM
					[dbo].[Menu]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Menu_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Menu_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Menu_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Menu table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Menu_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Title nvarchar (200)  = null ,

	@Alias varchar (200)  = null ,

	@Link nvarchar (200)  = null ,

	@ParentId int   = null ,

	@OrderIndex int   = null ,

	@IsVisible bit   = null ,

	@MenuType int   = null ,

	@MenuPosition varchar (10)  = null ,

	@Summary nvarchar (MAX)  = null ,

	@CreatedDate datetime   = null ,

	@UpdatedDate datetime   = null ,

	@CreatedUser varchar (10)  = null ,

	@UpdatedUser varchar (10)  = null ,

	@IsPublished bit   = null ,

	@Language varchar (10)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Alias]
	, [Link]
	, [ParentId]
	, [OrderIndex]
	, [IsVisible]
	, [MenuType]
	, [MenuPosition]
	, [Summary]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
	, [IsPublished]
	, [Language]
    FROM
	[dbo].[Menu]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
	AND ([Alias] = @Alias OR @Alias IS NULL)
	AND ([Link] = @Link OR @Link IS NULL)
	AND ([ParentId] = @ParentId OR @ParentId IS NULL)
	AND ([OrderIndex] = @OrderIndex OR @OrderIndex IS NULL)
	AND ([IsVisible] = @IsVisible OR @IsVisible IS NULL)
	AND ([MenuType] = @MenuType OR @MenuType IS NULL)
	AND ([MenuPosition] = @MenuPosition OR @MenuPosition IS NULL)
	AND ([Summary] = @Summary OR @Summary IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([CreatedUser] = @CreatedUser OR @CreatedUser IS NULL)
	AND ([UpdatedUser] = @UpdatedUser OR @UpdatedUser IS NULL)
	AND ([IsPublished] = @IsPublished OR @IsPublished IS NULL)
	AND ([Language] = @Language OR @Language IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Alias]
	, [Link]
	, [ParentId]
	, [OrderIndex]
	, [IsVisible]
	, [MenuType]
	, [MenuPosition]
	, [Summary]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
	, [IsPublished]
	, [Language]
    FROM
	[dbo].[Menu]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([Title] = @Title AND @Title is not null)
	OR ([Alias] = @Alias AND @Alias is not null)
	OR ([Link] = @Link AND @Link is not null)
	OR ([ParentId] = @ParentId AND @ParentId is not null)
	OR ([OrderIndex] = @OrderIndex AND @OrderIndex is not null)
	OR ([IsVisible] = @IsVisible AND @IsVisible is not null)
	OR ([MenuType] = @MenuType AND @MenuType is not null)
	OR ([MenuPosition] = @MenuPosition AND @MenuPosition is not null)
	OR ([Summary] = @Summary AND @Summary is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([CreatedUser] = @CreatedUser AND @CreatedUser is not null)
	OR ([UpdatedUser] = @UpdatedUser AND @UpdatedUser is not null)
	OR ([IsPublished] = @IsPublished AND @IsPublished is not null)
	OR ([Language] = @Language AND @Language is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Article table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_Get_List

AS


				
				SELECT
					[ID],
					[Title],
					[Alias],
					[Summary],
					[Description],
					[MenuId],
					[Thumbnail],
					[IsPublished],
					[OrderIndex],
					[Language],
					[IsAllowDelete],
					[IsHot],
					[PublishedDate],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Article]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Article table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Article]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[Title], O.[Alias], O.[Summary], O.[Description], O.[MenuId], O.[Thumbnail], O.[IsPublished], O.[OrderIndex], O.[Language], O.[IsAllowDelete], O.[IsHot], O.[PublishedDate], O.[CreatedDate], O.[UpdatedDate], O.[CreatedUser], O.[UpdatedUser]
				FROM
				    [dbo].[Article] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Article]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Article table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_Insert
(

	@Id int    OUTPUT,

	@Title nvarchar (200)  ,

	@Alias varchar (200)  ,

	@Summary nvarchar (500)  ,

	@Description ntext   ,

	@MenuId int   ,

	@Thumbnail varchar (200)  ,

	@IsPublished bit   ,

	@OrderIndex int   ,

	@Language varchar (10)  ,

	@IsAllowDelete bit   ,

	@IsHot bit   ,

	@PublishedDate datetime   ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (50)  ,

	@UpdatedUser varchar (50)  
)
AS


				
				INSERT INTO [dbo].[Article]
					(
					[Title]
					,[Alias]
					,[Summary]
					,[Description]
					,[MenuId]
					,[Thumbnail]
					,[IsPublished]
					,[OrderIndex]
					,[Language]
					,[IsAllowDelete]
					,[IsHot]
					,[PublishedDate]
					,[CreatedDate]
					,[UpdatedDate]
					,[CreatedUser]
					,[UpdatedUser]
					)
				VALUES
					(
					@Title
					,@Alias
					,@Summary
					,@Description
					,@MenuId
					,@Thumbnail
					,@IsPublished
					,@OrderIndex
					,@Language
					,@IsAllowDelete
					,@IsHot
					,@PublishedDate
					,@CreatedDate
					,@UpdatedDate
					,@CreatedUser
					,@UpdatedUser
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Article table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_Update
(

	@Id int   ,

	@Title nvarchar (200)  ,

	@Alias varchar (200)  ,

	@Summary nvarchar (500)  ,

	@Description ntext   ,

	@MenuId int   ,

	@Thumbnail varchar (200)  ,

	@IsPublished bit   ,

	@OrderIndex int   ,

	@Language varchar (10)  ,

	@IsAllowDelete bit   ,

	@IsHot bit   ,

	@PublishedDate datetime   ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (50)  ,

	@UpdatedUser varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Article]
				SET
					[Title] = @Title
					,[Alias] = @Alias
					,[Summary] = @Summary
					,[Description] = @Description
					,[MenuId] = @MenuId
					,[Thumbnail] = @Thumbnail
					,[IsPublished] = @IsPublished
					,[OrderIndex] = @OrderIndex
					,[Language] = @Language
					,[IsAllowDelete] = @IsAllowDelete
					,[IsHot] = @IsHot
					,[PublishedDate] = @PublishedDate
					,[CreatedDate] = @CreatedDate
					,[UpdatedDate] = @UpdatedDate
					,[CreatedUser] = @CreatedUser
					,[UpdatedUser] = @UpdatedUser
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Article table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Article] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_GetByMenuId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_GetByMenuId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_GetByMenuId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Article table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_GetByMenuId
(

	@MenuId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[ID],
					[Title],
					[Alias],
					[Summary],
					[Description],
					[MenuId],
					[Thumbnail],
					[IsPublished],
					[OrderIndex],
					[Language],
					[IsAllowDelete],
					[IsHot],
					[PublishedDate],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Article]
				WHERE
					[MenuId] = @MenuId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Article table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[Title],
					[Alias],
					[Summary],
					[Description],
					[MenuId],
					[Thumbnail],
					[IsPublished],
					[OrderIndex],
					[Language],
					[IsAllowDelete],
					[IsHot],
					[PublishedDate],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Article]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Article_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Article_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Article_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Article table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Article_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Title nvarchar (200)  = null ,

	@Alias varchar (200)  = null ,

	@Summary nvarchar (500)  = null ,

	@Description ntext   = null ,

	@MenuId int   = null ,

	@Thumbnail varchar (200)  = null ,

	@IsPublished bit   = null ,

	@OrderIndex int   = null ,

	@Language varchar (10)  = null ,

	@IsAllowDelete bit   = null ,

	@IsHot bit   = null ,

	@PublishedDate datetime   = null ,

	@CreatedDate datetime   = null ,

	@UpdatedDate datetime   = null ,

	@CreatedUser varchar (50)  = null ,

	@UpdatedUser varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Alias]
	, [Summary]
	, [Description]
	, [MenuId]
	, [Thumbnail]
	, [IsPublished]
	, [OrderIndex]
	, [Language]
	, [IsAllowDelete]
	, [IsHot]
	, [PublishedDate]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
    FROM
	[dbo].[Article]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
	AND ([Alias] = @Alias OR @Alias IS NULL)
	AND ([Summary] = @Summary OR @Summary IS NULL)
	AND ([MenuId] = @MenuId OR @MenuId IS NULL)
	AND ([Thumbnail] = @Thumbnail OR @Thumbnail IS NULL)
	AND ([IsPublished] = @IsPublished OR @IsPublished IS NULL)
	AND ([OrderIndex] = @OrderIndex OR @OrderIndex IS NULL)
	AND ([Language] = @Language OR @Language IS NULL)
	AND ([IsAllowDelete] = @IsAllowDelete OR @IsAllowDelete IS NULL)
	AND ([IsHot] = @IsHot OR @IsHot IS NULL)
	AND ([PublishedDate] = @PublishedDate OR @PublishedDate IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([CreatedUser] = @CreatedUser OR @CreatedUser IS NULL)
	AND ([UpdatedUser] = @UpdatedUser OR @UpdatedUser IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Alias]
	, [Summary]
	, [Description]
	, [MenuId]
	, [Thumbnail]
	, [IsPublished]
	, [OrderIndex]
	, [Language]
	, [IsAllowDelete]
	, [IsHot]
	, [PublishedDate]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
    FROM
	[dbo].[Article]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([Title] = @Title AND @Title is not null)
	OR ([Alias] = @Alias AND @Alias is not null)
	OR ([Summary] = @Summary AND @Summary is not null)
	OR ([MenuId] = @MenuId AND @MenuId is not null)
	OR ([Thumbnail] = @Thumbnail AND @Thumbnail is not null)
	OR ([IsPublished] = @IsPublished AND @IsPublished is not null)
	OR ([OrderIndex] = @OrderIndex AND @OrderIndex is not null)
	OR ([Language] = @Language AND @Language is not null)
	OR ([IsAllowDelete] = @IsAllowDelete AND @IsAllowDelete is not null)
	OR ([IsHot] = @IsHot AND @IsHot is not null)
	OR ([PublishedDate] = @PublishedDate AND @PublishedDate is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([CreatedUser] = @CreatedUser AND @CreatedUser is not null)
	OR ([UpdatedUser] = @UpdatedUser AND @UpdatedUser is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Contact_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Contact_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Contact_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Contact table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Contact_Get_List

AS


				
				SELECT
					[ID],
					[Title],
					[FullName],
					[Email],
					[PhoneNumber],
					[Description],
					[ContactType],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Contact]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Contact_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Contact_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Contact_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Contact table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Contact_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Contact]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[Title], O.[FullName], O.[Email], O.[PhoneNumber], O.[Description], O.[ContactType], O.[CreatedDate], O.[UpdatedDate], O.[CreatedUser], O.[UpdatedUser]
				FROM
				    [dbo].[Contact] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Contact]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Contact_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Contact_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Contact_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Contact table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Contact_Insert
(

	@Id int    OUTPUT,

	@Title varchar (250)  ,

	@FullName nvarchar (50)  ,

	@Email nvarchar (50)  ,

	@PhoneNumber nvarchar (20)  ,

	@Description nvarchar (MAX)  ,

	@ContactType varchar (20)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (50)  ,

	@UpdatedUser varchar (50)  
)
AS


				
				INSERT INTO [dbo].[Contact]
					(
					[Title]
					,[FullName]
					,[Email]
					,[PhoneNumber]
					,[Description]
					,[ContactType]
					,[CreatedDate]
					,[UpdatedDate]
					,[CreatedUser]
					,[UpdatedUser]
					)
				VALUES
					(
					@Title
					,@FullName
					,@Email
					,@PhoneNumber
					,@Description
					,@ContactType
					,@CreatedDate
					,@UpdatedDate
					,@CreatedUser
					,@UpdatedUser
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Contact_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Contact_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Contact_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Contact table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Contact_Update
(

	@Id int   ,

	@Title varchar (250)  ,

	@FullName nvarchar (50)  ,

	@Email nvarchar (50)  ,

	@PhoneNumber nvarchar (20)  ,

	@Description nvarchar (MAX)  ,

	@ContactType varchar (20)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@CreatedUser varchar (50)  ,

	@UpdatedUser varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Contact]
				SET
					[Title] = @Title
					,[FullName] = @FullName
					,[Email] = @Email
					,[PhoneNumber] = @PhoneNumber
					,[Description] = @Description
					,[ContactType] = @ContactType
					,[CreatedDate] = @CreatedDate
					,[UpdatedDate] = @UpdatedDate
					,[CreatedUser] = @CreatedUser
					,[UpdatedUser] = @UpdatedUser
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Contact_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Contact_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Contact_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Contact table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Contact_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Contact] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Contact_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Contact_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Contact_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Contact table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Contact_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[Title],
					[FullName],
					[Email],
					[PhoneNumber],
					[Description],
					[ContactType],
					[CreatedDate],
					[UpdatedDate],
					[CreatedUser],
					[UpdatedUser]
				FROM
					[dbo].[Contact]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Contact_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Contact_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Contact_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Contact table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Contact_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Title varchar (250)  = null ,

	@FullName nvarchar (50)  = null ,

	@Email nvarchar (50)  = null ,

	@PhoneNumber nvarchar (20)  = null ,

	@Description nvarchar (MAX)  = null ,

	@ContactType varchar (20)  = null ,

	@CreatedDate datetime   = null ,

	@UpdatedDate datetime   = null ,

	@CreatedUser varchar (50)  = null ,

	@UpdatedUser varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [FullName]
	, [Email]
	, [PhoneNumber]
	, [Description]
	, [ContactType]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
    FROM
	[dbo].[Contact]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
	AND ([FullName] = @FullName OR @FullName IS NULL)
	AND ([Email] = @Email OR @Email IS NULL)
	AND ([PhoneNumber] = @PhoneNumber OR @PhoneNumber IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
	AND ([ContactType] = @ContactType OR @ContactType IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([CreatedUser] = @CreatedUser OR @CreatedUser IS NULL)
	AND ([UpdatedUser] = @UpdatedUser OR @UpdatedUser IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [FullName]
	, [Email]
	, [PhoneNumber]
	, [Description]
	, [ContactType]
	, [CreatedDate]
	, [UpdatedDate]
	, [CreatedUser]
	, [UpdatedUser]
    FROM
	[dbo].[Contact]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([Title] = @Title AND @Title is not null)
	OR ([FullName] = @FullName AND @FullName is not null)
	OR ([Email] = @Email AND @Email is not null)
	OR ([PhoneNumber] = @PhoneNumber AND @PhoneNumber is not null)
	OR ([Description] = @Description AND @Description is not null)
	OR ([ContactType] = @ContactType AND @ContactType is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([CreatedUser] = @CreatedUser AND @CreatedUser is not null)
	OR ([UpdatedUser] = @UpdatedUser AND @UpdatedUser is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserType_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserType_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserType_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the UserType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserType_Get_List

AS


				
				SELECT
					[ID],
					[Title]
				FROM
					[dbo].[UserType]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserType_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserType_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserType_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the UserType table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserType_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[UserType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[Title]
				FROM
				    [dbo].[UserType] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[UserType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserType_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserType_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserType_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the UserType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserType_Insert
(

	@Id int    OUTPUT,

	@Title nvarchar (50)  
)
AS


				
				INSERT INTO [dbo].[UserType]
					(
					[Title]
					)
				VALUES
					(
					@Title
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserType_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserType_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserType_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the UserType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserType_Update
(

	@Id int   ,

	@Title nvarchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[UserType]
				SET
					[Title] = @Title
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserType_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserType_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserType_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the UserType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserType_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[UserType] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserType_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserType_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserType_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the UserType table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserType_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[Title]
				FROM
					[dbo].[UserType]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.UserType_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.UserType_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.UserType_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the UserType table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.UserType_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Title nvarchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [Title]
    FROM
	[dbo].[UserType]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [Title]
    FROM
	[dbo].[UserType]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([Title] = @Title AND @Title is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Customer_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Customer_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Customer_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Customer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Customer_Get_List

AS


				
				SELECT
					[ID],
					[Title],
					[Thumbnail],
					[Description],
					[CreatedDate],
					[UpdatedDate],
					[UpdatedUser],
					[CreatedUser]
				FROM
					[dbo].[Customer]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Customer_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Customer_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Customer_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Customer table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Customer_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([ID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [ID]'
				SET @SQL = @SQL + ' FROM [dbo].[Customer]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[ID], O.[Title], O.[Thumbnail], O.[Description], O.[CreatedDate], O.[UpdatedDate], O.[UpdatedUser], O.[CreatedUser]
				FROM
				    [dbo].[Customer] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[ID] = PageIndex.[ID]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Customer]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Customer_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Customer_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Customer_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Customer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Customer_Insert
(

	@Id int    OUTPUT,

	@Title nvarchar (150)  ,

	@Thumbnail varchar (50)  ,

	@Description nvarchar (MAX)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@UpdatedUser nvarchar (50)  ,

	@CreatedUser nvarchar (50)  
)
AS


				
				INSERT INTO [dbo].[Customer]
					(
					[Title]
					,[Thumbnail]
					,[Description]
					,[CreatedDate]
					,[UpdatedDate]
					,[UpdatedUser]
					,[CreatedUser]
					)
				VALUES
					(
					@Title
					,@Thumbnail
					,@Description
					,@CreatedDate
					,@UpdatedDate
					,@UpdatedUser
					,@CreatedUser
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Customer_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Customer_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Customer_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Customer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Customer_Update
(

	@Id int   ,

	@Title nvarchar (150)  ,

	@Thumbnail varchar (50)  ,

	@Description nvarchar (MAX)  ,

	@CreatedDate datetime   ,

	@UpdatedDate datetime   ,

	@UpdatedUser nvarchar (50)  ,

	@CreatedUser nvarchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Customer]
				SET
					[Title] = @Title
					,[Thumbnail] = @Thumbnail
					,[Description] = @Description
					,[CreatedDate] = @CreatedDate
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedUser] = @UpdatedUser
					,[CreatedUser] = @CreatedUser
				WHERE
[ID] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Customer_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Customer_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Customer_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Customer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Customer_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Customer] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Customer_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Customer_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Customer_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Customer table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Customer_GetById
(

	@Id int   
)
AS


				SELECT
					[ID],
					[Title],
					[Thumbnail],
					[Description],
					[CreatedDate],
					[UpdatedDate],
					[UpdatedUser],
					[CreatedUser]
				FROM
					[dbo].[Customer]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Customer_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Customer_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Customer_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Customer table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Customer_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Title nvarchar (150)  = null ,

	@Thumbnail varchar (50)  = null ,

	@Description nvarchar (MAX)  = null ,

	@CreatedDate datetime   = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedUser nvarchar (50)  = null ,

	@CreatedUser nvarchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Thumbnail]
	, [Description]
	, [CreatedDate]
	, [UpdatedDate]
	, [UpdatedUser]
	, [CreatedUser]
    FROM
	[dbo].[Customer]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([Title] = @Title OR @Title IS NULL)
	AND ([Thumbnail] = @Thumbnail OR @Thumbnail IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedUser] = @UpdatedUser OR @UpdatedUser IS NULL)
	AND ([CreatedUser] = @CreatedUser OR @CreatedUser IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [Title]
	, [Thumbnail]
	, [Description]
	, [CreatedDate]
	, [UpdatedDate]
	, [UpdatedUser]
	, [CreatedUser]
    FROM
	[dbo].[Customer]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([Title] = @Title AND @Title is not null)
	OR ([Thumbnail] = @Thumbnail AND @Thumbnail is not null)
	OR ([Description] = @Description AND @Description is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedUser] = @UpdatedUser AND @UpdatedUser is not null)
	OR ([CreatedUser] = @CreatedUser AND @CreatedUser is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

